from pymysql import Connection
from flask import Flask, request, jsonify
from flask_cors import CORS
import json
from collections import Counter

app = Flask(__name__)
CORS(app)

# 设置JSON编码
app.config['JSON_AS_ASCII'] = False
app.config['JSONIFY_MIMETYPE'] = "application/json;charset=utf-8"

def get_db_connection():
    return Connection(
        host='localhost',
        user='root',
        password='123456',
        database='movie_db',
        port=3306,
        charset='utf8mb4'
    )

def row_to_dict(row):
    return {
        'id': row[0],
        'movie_name': row[1],
        'release_year': row[2],
        'rating': float(row[3]),
        'rater_count': row[4],
        'director': row[5],
        'movie_type': row[6],
        'timing': row[7]
    }

# 按评分查询的接口
@app.route('/movies/rating', methods=['GET'])
def movies_by_rating():
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("""
            SELECT id, movie_name, rating
            FROM movies
            ORDER BY rating DESC
        """)
        movies = [{"id": row[0], "movie_name": row[1], "rating": float(row[2])} for row in cursor.fetchall()]
        # print(len(movies))
        # 计算平均评分
        average_rating = round(sum(movie['rating'] for movie in movies) / len(movies), 1) if movies else 0
        
        result = {
            "average_rating": average_rating,
            "movies": movies
        }
        return jsonify(result)
    except Exception as e:
        print(f"Error in movies_by_rating: {str(e)}")
        return jsonify({}), 500
    finally:
        if 'cursor' in locals():
            cursor.close()
        if 'conn' in locals():
            conn.close()

# 按年份分类的接口
@app.route('/movies/by-year', methods=['GET'])
def movies_by_year():
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("""
            SELECT release_year, COUNT(*) as count
            FROM movies
            GROUP BY release_year
            ORDER BY release_year
        """)
        year_data = [{"year": row[0], "count": row[1]} for row in cursor.fetchall()]
        
        # 计算总电影数
        total_movies = sum(item['count'] for item in year_data)
        
        # 添加总数到结果中
        result = {
            "year_stats": year_data,
            "total_movies": total_movies
        }
        return jsonify(result)
    except Exception as e:
        print(f"Error in movies_by_year: {str(e)}")
        return jsonify({}), 500
    finally:
        if 'cursor' in locals():
            cursor.close()
        if 'conn' in locals():
            conn.close()

# 按类型分类的接口
@app.route('/movies/by-type', methods=['GET'])
def movies_by_type():
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("""
            SELECT movie_type
            FROM movies
        """)
        # 获取所有类型并统计
        all_types = []
        for row in cursor.fetchall():
            if row[0]:  # 确保类型不为空
                types = row[0].split(',')
                all_types.extend(type.strip() for type in types if type.strip())
        
        # 统计每个类型的数量
        type_counts = Counter(all_types)
        # 转换为列表格式，包含类型和数量
        type_data = [{"type": type_name, "count": count} for type_name, count in type_counts.items()]
        
        # 计算总类型数（考虑一部电影可能有多个类型）
        total_types = sum(item['count'] for item in type_data)
        
        # 添加总数到结果中
        result = {
            "type_stats": type_data,
            "total_types": total_types
        }
        return jsonify(result)
    except Exception as e:
        print(f"Error in movies_by_type: {str(e)}")
        return jsonify({}), 500
    finally:
        if 'cursor' in locals():
            cursor.close()
        if 'conn' in locals():
            conn.close()

# 电影榜单前20的接口
@app.route('/movies/top20', methods=['GET'])
def movies_top20():
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("""
            SELECT id, movie_name, release_year, rating, rater_count, director, movie_type, timing
            FROM movies
            ORDER BY id
            LIMIT 20
        """)
        movies = [row_to_dict(row) for row in cursor.fetchall()]
        return jsonify(movies)
    except Exception as e:
        print(f"Error in movies_top20: {str(e)}")
        return jsonify([]), 500
    finally:
        if 'cursor' in locals():
            cursor.close()
        if 'conn' in locals():
            conn.close()

# 电影评价前50的接口
@app.route('/movies/top-rated', methods=['GET'])
def movies_top_rated():
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("""
            SELECT id, movie_name, release_year, rating, rater_count, director, movie_type, timing
            FROM movies
            ORDER BY rater_count DESC
            LIMIT 50
        """)
        movies = [row_to_dict(row) for row in cursor.fetchall()]
        return jsonify(movies)
    except Exception as e:
        print(f"Error in movies_top_rated: {str(e)}")
        return jsonify([]), 500
    finally:
        if 'cursor' in locals():
            cursor.close()
        if 'conn' in locals():
            conn.close()

@app.route('/', methods=['GET'])
def movie():
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("""
            SELECT id, movie_name, release_year, rating, rater_count, director, movie_type, timing
            FROM movies
        """)
        movies = [row_to_dict(row) for row in cursor.fetchall()]
        return jsonify(movies)
    except Exception as e:
        print(f"Error in movie: {str(e)}")
        return jsonify([]), 500
    finally:
        if 'cursor' in locals():
            cursor.close()
        if 'conn' in locals():
            conn.close()

# @app.route('/login',methods=['POST'])
# def login():
#     data = request.get_json()
#     username = data.get('username')
#     password = data.get('password')
#     print(username,password)
#     sql = 'select * from users where username = %s and password = %s'
#     cursor.execute(sql, (username, password))
#     resultLogin = cursor.fetchone()
#     if resultLogin:
#         return {
#             'code': 200,
#             'msg': '登入成功',
#             'user': username
#         }
#     else:
#         return {
#             'code': 401,
#             'msg': '用户名或密码错误'
#         }
if __name__ == '__main__':
    app.run(debug=True)